/**
 * HSQLDBManager.java 21 juil. 07
 *
 * Copyright (c) 2007 windu.2b <jcaddie@windu2b.org>. All Rights Reserved.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */
package org.windu2b.jcaddie.io.sgbd;

import java.io.*;
import java.sql.*;
import java.util.*;

import org.windu2b.jcaddie.model.*;

/**
 * @author windu.2b
 * 
 */
public class HSQLDBManager
{
	private Connection	                          con;


	private Statement	                          statement;


	private static HashMap<String, HSQLDBManager>	instance	            = new HashMap<String, HSQLDBManager>();


	public static String	                      DB_PROTOCOL	            = "jdbc:hsqldb:file:";


	public static String	                      DB_NAME	                = "jcaddie_bdd";


	public static String	                      DB_FILE	                = "/home/windu/workspace/JCaddie/lib/jcaddie.db";


	public static String	                      DB_USER	                = "sa";


	public static String	                      DB_PASSWD	                = "";


	public static String	                      DRIVER_NAME	            = "org.hsqldb.jdbcDriver";


	public static String	                      TABLE_CATEGORY_NAME	    = "category";


	public static String	                      FIELD_CATEGORY_ID	        = "id";


	public static String	                      FIELD_CATEGORY_NAME	    = "name";


	public static String	                      TABLE_PRODUCT_NAME	    = "product";


	public static String	                      FIELD_PRODUCT_ID	        = "id";


	public static String	                      FIELD_PRODUCT_NAME	    = "name";


	public static String	                      FIELD_PRODUCT_POIDS	    = "poids";


	public static String	                      FIELD_PRODUCT_CATEGORY_ID	= "category_id";


	public static String	                      TABLE_ENSEIGNE_NAME	    = "enseigne";


	public static String	                      FIELD_ENSEIGNE_ID	        = "id";


	public static String	                      FIELD_ENSEIGNE_NAME	    = "name";


	public static String	                      TABLE_SHOP_NAME	        = "shop";


	public static String	                      FIELD_SHOP_ID	            = "id";


	public static String	                      FIELD_SHOP_NAME	        = "name";


	public static String	                      FIELD_SHOP_ADRESSE	    = "adresse";


	public static String	                      FIELD_SHOP_ENSEIGNE_ID	= "enseigne_id";


	public static String	                      TABLE_PRICE_NAME	        = "price";


	public static String	                      FIELD_PRICE_ID	        = "id";


	public static String	                      FIELD_PRICE_VALUE	        = "value";


	public static String	                      FIELD_PRICE_DATE	        = "date";


	public static String	                      FIELD_PRICE_PRODUCT_ID	= "product_id";


	public static String	                      FIELD_PRICE_SHOP_ID	    = "shop_id";


	private static ResourceBundle	              resource	                = ResourceBundle
	                                                                                .getBundle( HSQLDBManager.class
	                                                                                        .getName() );


	private Caddie	                              caddie;


	HashMap<String, Category>	                  listCategory	            = new HashMap<String, Category>();


	HashMap<String, Enseigne>	                  listeEnseigne	            = new HashMap<String, Enseigne>();


	HashMap<String, Shop>	                      listShop	                = new HashMap<String, Shop>();




	/**
	 * Création d'une nouvelle instance HSQLDBManager
	 * 
	 * @param fileName
	 *            Le nom de la BDD à laquelle se connecter
	 */
	private HSQLDBManager( String fileName, Caddie caddie )
	{
		this.caddie = caddie;
		try
		{
			DriverManager.setLogWriter( new PrintWriter( System.out ) );
			Class.forName( DRIVER_NAME );

			con = DriverManager.getConnection( DB_PROTOCOL + fileName, DB_USER,
			        DB_PASSWD );

			statement = con.createStatement();

		}
		catch ( ClassNotFoundException ex )
		{
			System.out.println( "Driver introuvable : " );
			ex.printStackTrace();
		}
		catch ( SQLException ex )
		{
			System.out.println( "Erreur SQL : " );
			ex.printStackTrace();
		}
		catch ( Exception ex )
		{
			System.out.println( "Autre erreur : " );
			ex.printStackTrace();
		}
		finally
		{
			if ( con != null )
			{
				try
				{
					con.close();
				}
				catch ( Exception ex )
				{
					ex.printStackTrace();
				}
			}
		}
	}




	/**
	 * Renvoie une référence vers le manager pour la base de données dont le nom
	 * est <code>fileName</code> Si ce manager n'existe pas encore, il est
	 * créé et ajouté au pool de managers
	 * 
	 * @param name
	 *            le nom de fichier qui contient la BDD
	 * 
	 * @return une référence vers le manager qui gèrera la BDD désigné par
	 *         <code>fileName</code>
	 */
	public static synchronized HSQLDBManager getInstance( String name,
	        Caddie caddie )
	{
		if ( instance.get( name ) == null )
		    instance.put( name, new HSQLDBManager( name, caddie ) );

		return instance.get( name );
	}




	/**
	 * Crée la BDD avec pour nom <code>name</code>
	 * 
	 * @param name
	 *            le nom à donner à la BDD
	 */
	public void createDatabase( String name ) throws HSQLDBException
	{
		// requête de création de la table 'category'
		try
		{
			statement
			        .executeUpdate( "CREATE TABLE "
			                + TABLE_CATEGORY_NAME
			                + " ("
			                + FIELD_CATEGORY_ID
			                + " INT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY,"
			                + FIELD_CATEGORY_NAME + " VARCHAR(100))" );
			// Requête de création de la table 'product'
			statement
			        .executeUpdate( "CREATE TABLE "
			                + TABLE_PRODUCT_NAME
			                + " ("
			                + FIELD_PRODUCT_ID
			                + " INT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY,"
			                + FIELD_PRODUCT_NAME + " VARCHAR(100),"
			                + FIELD_PRODUCT_POIDS + "poids FLOAT,"
			                + FIELD_PRODUCT_CATEGORY_ID + " INT,"
			                + "FOREIGN KEY (" + FIELD_PRODUCT_CATEGORY_ID
			                + ") REFERENCES " + TABLE_CATEGORY_NAME + " ("
			                + FIELD_CATEGORY_ID + "))" );

			// Requête de création de la table 'shop'
			statement
			        .executeUpdate( "CREATE TABLE "
			                + TABLE_SHOP_NAME
			                + " ("
			                + FIELD_SHOP_ID
			                + " INT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY,"
			                + FIELD_SHOP_NAME + " VARCHAR(100),"
			                + FIELD_SHOP_ADRESSE + " VARCHAR(100),"
			                + FIELD_SHOP_ENSEIGNE_ID + " INT,"
			                + "FOREIGN KEY (" + FIELD_SHOP_ENSEIGNE_ID
			                + ") REFERENCES " + TABLE_ENSEIGNE_NAME + " ("
			                + FIELD_ENSEIGNE_ID + "))" );

			// Requête de création de la table 'enseigne'
			statement
			        .executeUpdate( "CREATE TABLE "
			                + TABLE_ENSEIGNE_NAME
			                + " ("
			                + FIELD_ENSEIGNE_ID
			                + " INT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY,"
			                + FIELD_ENSEIGNE_NAME + " VARCHAR(100))" );

			// Requête de création de la table 'price'
			statement
			        .executeUpdate( "CREATE TABLE "
			                + TABLE_PRICE_NAME
			                + " ("
			                + FIELD_PRICE_ID
			                + " INT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) PRIMARY KEY,"
			                + FIELD_PRICE_VALUE + " FLOAT," + FIELD_PRICE_DATE
			                + " DATETIME," + FIELD_PRICE_PRODUCT_ID + " INT,"
			                + FIELD_PRICE_SHOP_ID + " INT," + "FOREIGN KEY ("
			                + FIELD_PRICE_PRODUCT_ID + ") REFERENCES "
			                + TABLE_PRODUCT_NAME + " (" + FIELD_PRODUCT_ID
			                + ")," + "FOREIGN KEY (" + FIELD_PRICE_SHOP_ID
			                + ") REFERENCES " + TABLE_SHOP_NAME + " ("
			                + FIELD_SHOP_ID + ")" + ")" );
		}
		catch ( SQLException e )
		{
			// e.printStackTrace();
			throw new HSQLDBException( String.format( resource
			        .getString( "database.createError" ), name ), e );
		}
	}




	/**
	 * 
	 */
	public void insertCaddie() throws HSQLDBException
	{
		listCategory.clear();
		listeEnseigne.clear();
		listShop.clear();

		// Insertion des éléments du caddie
		for ( CaddiePieceOfProduct piece : caddie.getProduct() )
		{
			Category category = piece.getCategory();
			// On insère cette catégorie dans sa table seulement si cela n'a pas
			// déjà été fait
			if ( listCategory.get( category.getId() ) == null )
			{
				insertCategory( category );
				listCategory.put( category.getId(), category );
			}

			Shop shop = piece.getShop();
			if ( listShop.get( shop.getId() ) == null )
			{
				insertShop( shop );
				listShop.put( shop.getId(), shop );
			}

			String insert_product = String.format( "INSERT INTO %s"
			        + "( %s, %s, %s, %s )"
			        + " VALUES ( '%s', '%s', '%s', '%s' )", new Object[] {
			        TABLE_PRODUCT_NAME, FIELD_PRODUCT_ID, FIELD_PRODUCT_NAME,
			        FIELD_PRODUCT_POIDS, FIELD_PRODUCT_CATEGORY_ID,
			        piece.getId(), piece.getName(), piece.getPoids(),
			        category.getId() } );
			try
			{
				statement.executeUpdate( insert_product );
			}
			catch ( SQLException e )
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}




	/**
	 * Insère une <code>category</code> dans la BDD
	 * 
	 * @param category
	 */
	private void insertCategory( Category category )
	{
		String insert_category = String.format( "INSERT INTO %s"
		        + " ( %s, %s )" + " VALUES ( '%s', '%s' )", new Object[] {
		        TABLE_CATEGORY_NAME, FIELD_CATEGORY_ID, FIELD_CATEGORY_NAME,
		        category.getId(), category.getName() } );
		try
		{
			statement.executeUpdate( insert_category );
		}
		catch ( SQLException e )
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}




	/**
	 * Insère une <code>enseigne</code> dans la BDD
	 * 
	 * @param enseigne
	 */
	private void insertEnseigne( Enseigne enseigne )
	{
		String insertEnseigne = String.format( "INSERT INTO %s" + " ( %s, %s )"
		        + " VALUES ( '%s', '%s' )", new Object[] { TABLE_ENSEIGNE_NAME,
		        FIELD_ENSEIGNE_ID, FIELD_ENSEIGNE_NAME, enseigne.getId(),
		        enseigne.getName() } );
		try
		{
			statement.executeUpdate( insertEnseigne );
		}
		catch ( SQLException e )
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}




	/**
	 * Insère une <code>shop</code> dans la BDD
	 * 
	 * @param shop
	 */
	private void insertShop( Shop shop )
	{
		// insertio de l'enseigne si celle-ci n'a encore jamais été rencontrée
		Enseigne enseigne = shop.getEnseigne();
		if ( listeEnseigne.get( enseigne.getId() ) == null )
		{
			insertEnseigne( enseigne );
			listeEnseigne.put( enseigne.getId(), enseigne );
		}

		String insertShop = String.format( "INSERT INTO %s"
		        + " ( %s, %s, %s, %s )" + " VALUES ( '%s', '%s', '%s', '%s' )",
		        new Object[] { TABLE_SHOP_NAME, FIELD_SHOP_ID, FIELD_SHOP_NAME,
		                FIELD_SHOP_ADRESSE, FIELD_SHOP_ENSEIGNE_ID,
		                shop.getId(), shop.getName(), shop.getAddress(),
		                enseigne.getId() } );
		try
		{
			statement.executeUpdate( insertShop );
		}
		catch ( SQLException e )
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}




	/**
	 * Referme la connexion sur la DB HSQLDB
	 * 
	 * @throws SQLException
	 *             Renvoie une <code>SQLException</code> si la fermeture n'a
	 *             pu aboutir correctement
	 */
	public void close() throws SQLException
	{
		try
		{
			statement.executeQuery( "SHUTDOWN" );
			System.out.println( "Finalize" );
			statement.close();
		}
		catch ( SQLException e )
		{
			throw new SQLException(
			        "La connexion n'a pu être refermée correctement!" );
		}
	}




	@Override
	public void finalize() throws SQLException
	{
		close();
	}
}